﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Util;
using System.Collections;
namespace DAO
{
    /// <summary>
    /// Summary description for Album
    /// </summary>
    public class Album
    {
        DBConnection DBConnection;
        public Album()
        {
            DBConnection = new DBConnection();
            //
            // TODO: Add constructor logic here
            //
        }
        /// <summary>
        /// Get all DTO.Album instance from DB.
        /// </summary>
        /// <returns>List<> of all DTO.Album instances.</returns>
        public List<DTO.Album> GetAllAlbums()
        {
            Log.LogInfo("Begin GetAllAlbums()");
            String query = "SELECT * FROM [Album];";
            SqlDataReader reader = DBConnection.ExecuteReader(query);
            List<DTO.Album> albumDTOs = new List<DTO.Album>();
            try
            {
                while (reader.Read())
                {
                    albumDTOs.Add(new DTO.Album(reader));
                }
            }
            catch (Exception ex)
            {
                Log.LogError("Error GetAllAlbums(): " + ex.StackTrace);
            }

            Log.LogInfo("End GetAllAlbums()");
            DBConnection.Close();
            return albumDTOs;
        }
        /// <summary>
        /// Get DTO.Album instance from DB.
        /// </summary>
        /// <param name="ID">Album ID</param>
        /// <returns>DTO.Album instance which has the specific ID.</returns>
        public DTO.Album GetAlbum(String ID)
        {
            Log.LogInfo("Begin GetAlbum(String ID)");
            String query = "SELECT * FROM [Album] WHERE [Album].[ID]='" + ID + "';";
            SqlDataReader reader = DBConnection.ExecuteReader(query);
            DTO.Album albumDTO = null;
            try
            {
                if (reader.Read())
                    albumDTO = new DTO.Album(reader);
            }
            catch (Exception ex)
            {
                Log.LogError("Error GetAlbum(String ID): " + ex.Message);
            }

            Log.LogInfo("End GetAlbum(String ID)");
            DBConnection.Close();
            return albumDTO;
        }
        public ArrayList GetAlbumWithUserId(string userID)
        {
            Log.LogInfo("Begin GetAlbumWithUserId");
            String query = "Select * from Album where UserID='" + userID + "'";
            SqlDataReader reader = DBConnection.ExecuteReader(query);
            DTO.Album albumDTO = null; 
            ArrayList arrayList = new ArrayList();
            try
            {
                while(reader.Read())
                {
                    albumDTO = new DTO.Album(reader);
                    arrayList.Add(albumDTO);
                }
            }
            catch (Exception ex)
            {
                Log.LogError("Error GetAlbumWithUserId(String userID)");
            }
            Log.LogInfo("End GetAlbumWithUserId(String userID)");
            DBConnection.Close();
            return arrayList;
        }

        /// <summary>
        /// Insert Album into database.
        /// </summary>
        /// <param name="Album">DTO.Album instance to insert into database.</param>
        public int InsertAlbum(DTO.Album Album)
        {
            Log.LogInfo("Begin InsertAlbum()");
            String query = "";
            query += "INSERT INTO AlBUM (UserID, Name) VALUES ('" + Album.UserID + "','" + Album.Name + "')";
            query += " SELECT @@IDENTITY as 'AlbumID' ";
            int result = -1;
            //int result = DBConnection.ExecuteNonQuery(query);
            SqlDataReader sqlDataReader = DBConnection.ExecuteReader(query);
            try
            {
                if (sqlDataReader.Read())  result= int.Parse (sqlDataReader["AlbumID"].ToString());
            }
            catch
            {
                Log.LogError("Error InsertAlbum(DTO.Album Album Album)");
            }
            DBConnection.Close();
            Log.LogInfo("Insert " + result.ToString() + "row(s).");
            Log.LogInfo("End InsertAlbum()");
            return result;
        }

        /// <summary>
        /// Update Album info into database.
        /// </summary>
        /// <param name="Album">Album instance to be updated.</param>
        public int UpdateAlbum(DTO.Album Album)
        {
            Log.LogInfo("Begin Updatealbum()");
            String query = "UPDATE ALBUM SET UserID = '" + Album.UserID + "','" + " Name = '" + Album.Name + "' WHERE ID = '" + Album.ID + "'";
            int result = DBConnection.ExecuteNonQuery(query);
            Log.LogInfo("Update " + result.ToString() + "row(s).");
            DBConnection.Close();
            Log.LogInfo("End InsertAlbum()");
            return result;
        }
        /// <summary>
        /// Delete a specific Album from DB.
        /// </summary>
        /// <param name="Album">The Album to be deleted.</param>
        public int DeleteAlbum(DTO.Album Album)
        {
            Log.LogInfo("Begin DeleteAlbum()");
            String query = "DELETE FROM [Album] WHERE [Album].[ID]='" + Album.ID + "';";
            int result = DBConnection.ExecuteNonQuery(query);
            Log.LogInfo("Deleted " + result.ToString() + "row(s).");
            DBConnection.Close();
            Log.LogInfo("End DeleteAlbum()");
            return result;
        }

        /// <summary>
        /// Delete a Album in database using ID.
        /// </summary>
        /// <param name="ID">Album ID.</param>
        public void DeleteAlbum(String ID)
        {
            Log.LogInfo("Begin DeleteAlbum()");
            String query = "DELETE FROM [Album] WHERE [Album].[ID]='" + ID + "';";
            int result = DBConnection.ExecuteNonQuery(query);
            Log.LogInfo("Deleted " + result.ToString() + "row(s).");
            DBConnection.Close();
            Log.LogInfo("End DeleteAlbum()");
        }

    }
}